package com.stock.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import com.stock.model.StockStatus;
import com.stock.model.StockVO;

public class StockDAO extends BaseDAO{
	
	public List<StockVO> getStockList(String symbol ,Date startDate ,Date endDate) throws SQLException {
		String sql = "select * from stock where symbol = ? and trade_date BETWEEN ? and ? order by trade_date asc";
		 
		return getQueryRunner().query(sql , 
			new ResultSetHandler<List<StockVO>>() {
				@Override
				public List<StockVO> handle(ResultSet result) throws SQLException {
					List<StockVO> list = new ArrayList<>();
					while(result.next()) {
						StockVO vo =  new StockVO();
						vo.setId(result.getInt("id"));
						vo.setTrade_date(result.getDate("TRADE_DATE"));
						vo.setSymbol(result.getString("SYMBOL"));
						vo.setOpen(result.getDouble("OPEN"));
						vo.setHigh(result.getDouble("HIGH"));
						vo.setLow(result.getDouble("LOW"));
						vo.setPrice(result.getDouble("PRICE"));
						vo.setHighPoint1(result.getDouble("HIGH_POINT1"));
						vo.setHighPoint2(result.getDouble("HIGH_POINT2"));
						vo.setLowPoint1(result.getDouble("LOW_POINT1"));
						vo.setLowPoint2(result.getDouble("LOW_POINT2"));
						vo.setHisHighPoint(result.getDouble("HIS_HIGH_POINT"));
						vo.setNote(result.getString("NOTE"));
						vo.setSignalStatus(result.getInt("SIGNAL_STATUS"));
						vo.setCheckPoint(result.getString("CHICK_POINT"));
						String tagName = result.getString("TAG_NAME"); 
						if (tagName != null) {
							vo.setStockStatus(StockStatus.valueOf(tagName));
						}
						list.add(vo);
					}
					return list;
				}
			},
			new Object[]{symbol, startDate, endDate}
		);
	}
	
	public StockVO getStockUp(String symbol, String date) throws SQLException {
		String sql = "select * from stock where symbol = ?  and  trade_date = (select max(trade_date) from stock where symbol =? and trade_date < ?)";
		 
		return getQueryRunner().query(sql , 
			new ResultSetHandler<StockVO>() {
				@Override
				public StockVO handle(ResultSet result) throws SQLException {
					
					if (!result.next())return null; 
					
					StockVO vo =  new StockVO();
					vo.setId(result.getInt("id"));
					vo.setTrade_date(result.getDate("TRADE_DATE"));
					vo.setSymbol(result.getString("SYMBOL"));
					vo.setOpen(result.getDouble("OPEN"));
					vo.setHigh(result.getDouble("HIGH"));
					vo.setLow(result.getDouble("LOW"));
					vo.setPrice(result.getDouble("PRICE"));
					vo.setHighPoint1(result.getDouble("HIGH_POINT1"));
					vo.setHighPoint2(result.getDouble("HIGH_POINT2"));
					vo.setLowPoint1(result.getDouble("LOW_POINT1"));
					vo.setLowPoint2(result.getDouble("LOW_POINT2"));
					vo.setHisHighPoint(result.getDouble("HIS_HIGH_POINT"));
					vo.setNote(result.getString("NOTE"));
					vo.setSignalStatus(result.getInt("SIGNAL_STATUS"));
					vo.setCheckPoint(result.getString("CHICK_POINT"));
					String tagName = result.getString("TAG_NAME"); 
					if (tagName != null) {
						vo.setStockStatus(StockStatus.valueOf(tagName));
					}
					return vo;
					 
				}
			},
			new Object[]{symbol, symbol, date}
		);
	}
	
	
	 
	 
	public void batchInsertStackVO(List<StockVO> stockList) throws SQLException {
		String sql = "INSERT IGNORE INTO STOCK"
				   + "(TRADE_DATE, SYMBOL, OPEN, HIGH, LOW, PRICE, VOLUME,"
				   + "LOW_POINT1,LOW_POINT2,HIGH_POINT1,HIGH_POINT2,HIS_HIGH_POINT,CHICK_POINT,NOTE,"
				   + "TAG_NAME,TAG_DESC,SIGNAL_STATUS)"
				   + "VALUES (?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?,?,?,?)";
		
		PreparedStatement ps = getConnection().prepareStatement(sql);
		
		QueryRunner queyr = this.getQueryRunner(); 
		
		for (StockVO vo : stockList) {
			queyr.fillStatementWithBean(ps, vo, 
				"trade_date" ,"symbol", "open", "high", "low" , "price","volume",
				"lowPoint1" ,"lowPoint2","highPoint1" ,"highPoint2","hisHighPoint",
				"checkPoint","note", "tagName","tagDesc","signalStatus"
			);
			ps.addBatch();
		}
		ps.executeBatch();
		ps.close();
	}
	 
	public void saveOrUpdateStockVO(StockVO stockVO) throws SQLException {
		String sql = " INSERT INTO STOCK"
				   + " (TRADE_DATE, SYMBOL, OPEN, HIGH, LOW, PRICE, VOLUME,"
				   + " LOW_POINT1,LOW_POINT2,HIGH_POINT1,HIGH_POINT2,HIS_HIGH_POINT,CHICK_POINT,NOTE,"
				   + " TAG_NAME,TAG_DESC,SIGNAL_STATUS)"
				   + " VALUES (?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?,?,?,?) "
				   + " ON DUPLICATE KEY UPDATE "
				   + " OPEN=?, HIGH=?,LOW=?,PRICE=?,VOLUME=?,"
				   + " LOW_POINT1=?, LOW_POINT2=?,HIGH_POINT1=?, HIGH_POINT2=?,HIS_HIGH_POINT=?,"
				   + " CHICK_POINT=?, NOTE=?,TAG_NAME=?, TAG_DESC=?,SIGNAL_STATUS=?,UPDATE_TIME=CURRENT_TIMESTAMP";
		
		PreparedStatement ps = getConnection().prepareStatement(sql);
		 
		this.getQueryRunner().fillStatementWithBean(ps, stockVO, 
			"trade_date" ,"symbol", "open", "high", "low" , "price","volume",
			"lowPoint1" ,"lowPoint2","highPoint1" ,"highPoint2","hisHighPoint",
			"checkPoint","note", "tagName","tagDesc","signalStatus",
			
			"open", "high", "low" , "price","volume",
			"lowPoint1" ,"lowPoint2","highPoint1" ,"highPoint2","hisHighPoint",
			"checkPoint","note", "tagName","tagDesc","signalStatus"
		);
		 
		ps.execute();
		ps.close();
	}

	 
}



 